﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class MaritalStatusService
    {
       
        public bool SearchTable()
        {
            SqlConnection conn = ConnPoll.GetConn();
            bool tempbool = false;
            string sql = "select * from MaritalStatus";
            ConnPoll.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                tempbool = true;
                break;
            }
            dr.Close();
            com = null;
            ConnPoll.Close();
            return tempbool;
        }

        #region 创建婚姻状况表
        /// <summary>
        /// 创建婚姻状况表
        /// </summary>
        /// <param name="UserName">用户名</param>
        public void CreateMaritalStatus()
        {
            SqlConnection conn = ConnPoll.GetConn();
            try
            {
                ConnPoll.Open();
                string tbna = "MaritalStatus";
                string sql = " use bds249611391_db"
                    + " create table " + tbna
                    + " ("
                    + " MaritalStatusId int not null identity(1,1),"//主键Id
                    + " Explain nvarchar(50) null,"
                    + " MaritalStatusName nvarchar(50) not null"//状况详情
                    + " )"
                    + " use bds249611391_db"
                    + " alter table " + tbna
                    + " add constraint PK_MaritalStatusId primary key (MaritalStatusId)";
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com = null;
                ConnPoll.Close();
                Insert(new MaritalStatus() { Explain="婚姻状况", MaritalStatusName=string.Empty });
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 查找库中是否已有此内容
        /// <summary>
        /// 查找库中是否已有此内容
        /// </summary>
        /// <param name="ms">婚姻状况类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Search(MaritalStatus ms, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) from MaritalStatus" + UserName + " where MaritalStatusName=@MaritalStatusName";
            return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql, new SqlParameter("@MaritalStatusName", ms.MaritalStatusName)));
        }
        #endregion

        #region 插入信息
        /// <summary>
        /// 插入婚姻状况信息
        /// </summary>
        /// <param name="ms">婚姻状况类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Insert(MaritalStatus ms)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into MaritalStatus (Explain,MaritalStatusName) values (@Explain,@MaritalStatusName)";
            if (ms.Explain == string.Empty)
                sql = "insert into MaritalStatus (Explain,MaritalStatusName) values (default,@MaritalStatusName)";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@Explain",ms.Explain),
                new SqlParameter("@MaritalStatusName",ms.MaritalStatusName)
            };
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息（根据ID）
        /// </summary>
        /// <param name="i">婚姻状况类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Delete(MaritalStatus ms)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete MaritalStatus where MaritalStatusName=@MaritalStatusName";
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql,new SqlParameter("@MaritalStatusName", ms.MaritalStatusName));
        }
        #endregion

        #region 修改信息
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="ms">婚姻状况类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Set(MaritalStatus ms,string setStr)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update MaritalStatus set MaritalStatusName=@setStr where MaritalStatusName=@MaritalStatusName";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@setStr",setStr),
                new SqlParameter("@MaritalStatusName",ms.MaritalStatusName),
            };
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 查询信息
        /// <summary>
        /// 查询信息
        /// </summary>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public List<MaritalStatus> SearchAll()
        {
            SqlConnection conn = ConnPoll.GetConn();
            List<MaritalStatus> lms = new List<MaritalStatus>();
            string sql = "select * from MaritalStatus";
            SqlDataReader dr = SQLHelper.ExecuteReader(conn,System.Data.CommandType.Text,sql);
            while(dr.Read())
            {
                if (dr["MaritalStatusName"].ToString() == string.Empty)
                    continue;
                MaritalStatus m = new MaritalStatus();
                m.MaritalStatusId = Convert.ToInt32(dr["MaritalStatusId"]);
                m.MaritalStatusName = dr["MaritalStatusName"].ToString();
                lms.Add(m);
            }
            dr.Close();
            return lms;
        }
        #endregion


        #region 查询标题
        /// <summary>
        /// 查询标题
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public string SearchTitle()
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select Explain from MaritalStatus";
            return SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql).ToString();
        }
        #endregion
    }
}
